package db;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

import processing.core.PApplet;

import applet.Artist;
import applet.ArtistButton;
import applet.Country;
import applet.FilterButton;
import applet.GLOBAL;

//import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

public class DBConnection implements Runnable{
    
    private Connection conn = null;
    private String dbName = "radiogaga";
    private boolean connected = false;
    private PoolingDataSource dataSource = null;
    
    public DBConnection() {
    	 try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	dataSource = setupDataSource("jdbc:mysql://lhc.servegame.com:3307/"+dbName, "radiogaga", "radiogaga");
    	try {
			conn = dataSource.getConnection();
			//conn = new net.sf.log4jdbc.ConnectionSpy(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
    
    private PoolingDataSource setupDataSource(String connectURI, String user,
			String password) {
		//
		// First, we'll need a ObjectPool that serves as the
		// actual pool of connections.
		//
		// We'll use a GenericObjectPool instance, although
		// any ObjectPool implementation will suffice.
		//
		GenericObjectPool.Config config = new GenericObjectPool.Config();
		config.maxActive = 150;
		config.maxIdle = 100;
		config.minIdle = 30;
		config.maxWait = 1000;

		ObjectPool connectionPool = new GenericObjectPool(null, config);

		//
		// Next, we'll create a ConnectionFactory that the
		// pool will use to create Connections.
		// We'll use the DriverManagerConnectionFactory,
		// using the connect string passed in the command line
		// arguments.
		//
//		Properties p = new Properties();
//		p.setProperty("user", SQLConstants.USER_NAME);
//		p.setProperty("password", SQLConstants.PASSWORD);
//		p.setProperty("useUnicode", "true");
//		p.setProperty("characterEncoding", "UTF-8");
		
		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
				connectURI, user, password);
//		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
//				connectURI, p);
		//
		// Now we'll create the PoolableConnectionFactory, which wraps
		// the "real" Connections created by the ConnectionFactory with
		// the classes that implement the pooling functionality.
		//
		PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
				connectionFactory, connectionPool, null, null, false, true);

		//
		// Finally, we create the PoolingDriver itself,
		// passing in the object pool we created.
		//
		PoolingDataSource poolingDataSource = new PoolingDataSource(connectionPool);

		return poolingDataSource;
	}
    
    public boolean connectToDB() {
        try {
        	conn = dataSource.getConnection();
            conn.setReadOnly(true);
            connected = true;
            return true;
        } catch (Exception e) {
            //System.out.println(e);
            e.printStackTrace();
            return false;
        }
    }
    
    
    public void closeConnToDB(){
        try {
            conn.close();
            connected = false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public Connection getConnection(){
        return conn;
    }
        
    public void getCountryTimes(){
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT * FROM countries_times;");
			ResultSet r = prep.executeQuery();
			Country c = null;
			while(r.next()){
				
				String name = r.getString("country");
				if(c == null || !c.name.equals(name)){
					double lat = r.getDouble("latitude");
					double lon = r.getDouble("longitude");
					String capitol = r.getString("capitol");
					int tz = r.getInt("time_zone");
					c = new Country(name, lat, lon, capitol, tz);
				}
				
				if(!GLOBAL.countries_times.contains(c)){
					GLOBAL.countries_times.add(c);
				}
				else{
					int index = GLOBAL.countries_times.indexOf(c);
					c = GLOBAL.countries_times.get(index);
				}
				
				c.addCount(r.getInt("hour"), r.getInt("plays"));
				
			}
    	}
    	catch (Exception e) {
			// TODO: handle exception
    		e.printStackTrace();
		}
    }
    
    public void getArtistInfo(Artist a){
    	//Please search the records count to Artist.recordsCountByAge, Artist.recordsCountBySex and Artist.recordsCountByCountries
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT gender,count(*) as count FROM artists_users INNER JOIN users ON users.id = artists_users.user_id WHERE artist_id = ? AND gender != '' GROUP BY gender;");
			prep.setInt(1, a.id);
			ResultSet r = prep.executeQuery();
			int i = 0;
			while(r.next()){
				a.recordsCountBySex[i] = r.getInt("count");
				i++;
			}
			r.close();
			prep.close();
			prep = conn.prepareStatement("SELECT country,count(*) as count FROM artists_users INNER JOIN users ON users.id = artists_users.user_id WHERE artist_id = ? AND country != '' GROUP BY country;");
			prep.setInt(1, a.id);
			r = prep.executeQuery();
			
			i = 0;
			while(r.next()){
				//System.out.println(r.getInt("country"));
				a.recordsCountByCountries.put(r.getString("country"),(long) r.getInt("count"));
				i++;
			}
			r.close();
			prep.close();
			prep = conn.prepareStatement("SELECT age_range,count(*) as count FROM artists_users INNER JOIN users ON users.id = artists_users.user_id WHERE artist_id = ? AND age_range IS NOT NULL GROUP BY age_range;");
			prep.setInt(1, a.id);
			r = prep.executeQuery();
			
			i = 0;
			while(r.next()){
				a.recordsCountByAge[i] = r.getInt("count");
				i++;
			}
			r.close();
			prep.close();
    	}
    	catch (Exception e) {
			// TODO: handle exception
		}
    }
    
    public void getAllCountries(){
    	GLOBAL.countryButtons.clear();
 //   	ArrayList<ArtistButton> arr = new ArrayList<ArtistButton>();
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT * FROM countries;");
			ResultSet r = prep.executeQuery();
			int i = 0;
			while(r.next()){
				
				FilterButton a = new FilterButton();
				a.x = 550;
				a.y = 55 + i*40;
				a.width = 50;
				a.height = 19;
				a.setLabel(r.getString("name"));
				a.id = r.getInt("id"); //for GLOBAL use
				if(GLOBAL.curCountryId<0)
				{
					a.active = true;
				}
				else
				{
					if(i ==GLOBAL.curCountryId)
						a.active = true;
					else
						a.active = false;
				}
				/*if(i ==9)
					{
						a.active = true;
					}
				else
					a.active = false;*/
				PApplet.println(i+": "+a.active+" "+GLOBAL.curCountryId);
				GLOBAL.countryButtons.add(a);
				/*System.out.println(r.getInt("id"));
				System.out.println(r.getString("name"));
				System.out.println(r.getDouble("latitude"));
				System.out.println(r.getDouble("longitude"));
				System.out.println(r.getString("capitol"));
				System.out.println(r.getInt("time_zone"));*/
				i++;
			}
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    }
    
    public void getCountry(int id){
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT * FROM countries WHERE id = ?");
			prep.setLong(1, id);
			ResultSet r = prep.executeQuery();
			while(r.next()){
				/*System.out.println(r.getInt("id"));
				System.out.println(r.getString("name"));
				System.out.println(r.getDouble("latitude"));
				System.out.println(r.getDouble("longitude"));
				System.out.println(r.getString("capitol"));
				System.out.println(r.getInt("time_zone"));*/
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    }
    
    public String getCountryName(int id){
    	connectToDB();
    	PreparedStatement prep = null;
    	String rtn="";
    	try {
			prep = conn.prepareStatement("SELECT * FROM countries WHERE id = ?");
			prep.setLong(1, id);
			ResultSet r = prep.executeQuery();
			while(r.next()){
				rtn = r.getString("name");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    	return rtn;
    }
    
    public void getArtist(int id){
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT * FROM temp_artists WHERE id = ?");
			prep.setLong(1, id);
			ResultSet r = prep.executeQuery();
			while(r.next()){
				/*System.out.println(r.getInt("id"));
				System.out.println(r.getString("mbid"));
				System.out.println(r.getString("name"));
				System.out.println(r.getInt("total_plays"));*/
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    }

    public Artist getArtistNameUsingId(int id){
    	connectToDB();
    	PreparedStatement prep = null;
    	Artist a = new Artist(0,null,null,0);
    	try {
			prep = conn.prepareStatement("SELECT * FROM temp_artists WHERE id = ?");
			prep.setLong(1, id);
			ResultSet r = prep.executeQuery();
			while(r.next()){
	//			System.out.println(r.getInt("id"));
	//			System.out.println(r.getString("mbid"));
	//			System.out.println(r.getString("name"));
	//			System.out.println(r.getInt("total_plays"));
				a.id = r.getInt("id");
				a.name= r.getString("name");
				a.mbid = r.getString("mbid");
				a.playCount = r.getInt("total_plays");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    	return a;
    }
    
    public void getArtistNamesByTotalPopularityThreaded(final long start, final long limit){
    	new Thread(new Runnable() {
            public void run() { getArtistNamesByTotalPopularity(start, limit); }
        }).start();
    }
    
    public void getArtistNamesByTotalPopularity(long start, long limit){
    	getArtistNamesByTotalPopularity(start, limit, true);
    }
    
    public void getArtistNamesByTotalPopularity(long start, long limit, boolean clear){
    	if(clear)
    		GLOBAL.items.clear();
 //   	ArrayList<ArtistButton> arr = new ArrayList<ArtistButton>();
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
			prep = conn.prepareStatement("SELECT * FROM temp_artists LIMIT ?,?");
			prep.setLong(1, start);
			prep.setLong(2, limit);
			ResultSet r = prep.executeQuery();
			while(r.next()){
		  	     ArtistButton a = new ArtistButton();
			     a.setLabel(r.getString("name"));
			     a.artist = new Artist(r.getInt("id"), r.getString("mbid"), r.getString("name"), r.getInt("total_plays"));
			     GLOBAL.addArtist(a);
			}
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	closeConnToDB();
    }
    public void getArtistsPopularityByAgeGenderCountry(int age_range_id, String gender, String country, long start, long limit){
    	getArtistsPopularityByAgeGenderCountry(age_range_id, gender, country, start, limit, true);
    }
    
    
    public void getArtistsPopularityByAgeGenderCountry(int age_range_id, String gender, String country, long start, long limit, boolean clear){
    	if(clear)
    		GLOBAL.items.clear();
    	if(gender.equalsIgnoreCase(("Male")))
    			gender = "m";
    	if(gender.equalsIgnoreCase(("Female")))
			gender = "f";
    	if(gender.equalsIgnoreCase(("Both")))
			gender = "";
    	if(country.equalsIgnoreCase(("All")))
			country = "";
    	if(age_range_id == -1)
    		age_range_id = 0;
    	connectToDB();
    	PreparedStatement prep = null;
    	try {
    		if(age_range_id == 0 && gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT id as artist_id, name as artist_name FROM temp_artists LIMIT ?,?");
    			prep.setLong(1, start);
    			prep.setLong(2, limit);
    		}else
    		if(age_range_id != 0 && gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE age_range = (?) LIMIT ?,?");
    			prep.setInt(1, age_range_id);
    			prep.setLong(2, start);
    			prep.setLong(3, limit);
    		}else
			if(age_range_id == 0 && !gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE gender = (?) LIMIT ?,?");
    			prep.setString(1, gender);
    			prep.setLong(2, start);
    			prep.setLong(3, limit);
    		}else
    		if(age_range_id == 0 && gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE country = (?) LIMIT ?,?");
    			prep.setString(1, country);
    			prep.setLong(2, start);
    			prep.setLong(3, limit);
    		}else
    		if(age_range_id != 0 && !gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE age_range = (?) AND gender = (?) LIMIT ?,?");
    			prep.setInt(1, age_range_id);
    			prep.setString(2, gender);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
    		if(age_range_id != 0 && gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE age_range = (?) AND country = (?) LIMIT ?,?");
    			prep.setInt(1, age_range_id);
    			prep.setString(2, country);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
    		if(age_range_id == 0 && !gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE gender = (?) AND country = (?) LIMIT ?,?");
    			prep.setString(1, gender);
    			prep.setString(2, country);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
    		if(age_range_id != 0 && !gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id, artist_name FROM demographics WHERE gender = (?) AND age_range = (?) AND country = (?) LIMIT ?,?");
    			prep.setString(1, gender);
    			prep.setInt(2, age_range_id);
    			prep.setString(3, country);
    			prep.setLong(4, start);
    			prep.setLong(5, limit);
    		}
			
			ResultSet r = prep.executeQuery();
			while(r.next()){
				int id = r.getInt("artist_id");
				String name = r.getString("artist_name");
				//System.out.println("id="+id + " name = "+name);
		  	     ArtistButton a = new ArtistButton();
			     a.setLabel(r.getString("artist_name"));
			     a.artist = new Artist(r.getInt("artist_id"), "", r.getString("artist_name"), -1);
			     GLOBAL.addArtist(a);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    }
    
    public void getArtistsRecommendations(int artist_id, int age_range_id, String gender, String country, long start, long limit){
    	int random = (int) (Math.random() * 1500);
    	start += random;
    	GLOBAL.artistRecommendationsIds.clear();
    	if(gender.equalsIgnoreCase(("Male")))
    			gender = "m";
    	if(gender.equalsIgnoreCase(("Female")))
			gender = "f";
    	if(gender.equalsIgnoreCase(("Both")))
			gender = "";
    	if(country.equalsIgnoreCase(("All")))
			country = "";
    	if(age_range_id == -1)
    		age_range_id = 0;
    	connectToDB();
    	PreparedStatement prep = null;
    	//
    	try {
    		if(age_range_id == 0 && gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT artist_id2 as artist_id, name as artist_name FROM recommendations AS r INNER JOIN temp_artists AS a ON artist_id2 = a.id WHERE artist_id1 = ? LIMIT ?,?");
    			prep.setInt(1, artist_id);
    			prep.setLong(2, start);
    			prep.setLong(3, limit);
    		}else
    		if(age_range_id != 0 && gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender IS NULL AND country IS NULL AND age_range = ? LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setInt(2, age_range_id);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
			if(age_range_id == 0 && !gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender = ? AND country IS NULL AND age_range IS NULL LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, gender);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
    		if(age_range_id == 0 && gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender IS NULL AND country = ? AND age_range IS NULL LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, country);
    			prep.setLong(3, start);
    			prep.setLong(4, limit);
    		}else
    		if(age_range_id != 0 && !gender.equals("") && country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender = ? AND country IS NULL AND age_range = ? LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, gender);
    			prep.setInt(3, age_range_id);
    			prep.setLong(4, start);
    			prep.setLong(5, limit);
    		}else
    		if(age_range_id != 0 && gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender IS NULL AND country = ? AND age_range = ? LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, country);
    			prep.setInt(3, age_range_id);
    			prep.setLong(4, start);
    			prep.setLong(5, limit);
    		}else
    		if(age_range_id == 0 && !gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender = ? AND country = ? AND age_range IS NULL LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, gender);
    			prep.setString(3, country);
    			prep.setLong(4, start);
    			prep.setLong(5, limit);
    		}else
    		if(age_range_id != 0 && !gender.equals("") && !country.equals("")){
    			prep = conn.prepareStatement("SELECT DISTINCT artist_id, artist_name FROM recommendations AS r INNER JOIN demographics AS a ON artist_id2 = a.artist_id WHERE artist_id1 = ? AND gender = ? AND country = ? AND age_range = ? LIMIT ?,?;");
    			prep.setInt(1, artist_id);
    			prep.setString(2, gender);
    			prep.setString(3, country);
    			prep.setInt(4, age_range_id);
    			prep.setLong(5, start);
    			prep.setLong(6, limit);
    		}
			
			ResultSet r = prep.executeQuery();
			while(r.next()){
				int id = r.getInt("artist_id");
				String name = r.getString("artist_name");
				//System.out.println("id="+id + " name = "+name);
				GLOBAL.artistRecommendationsIds.add(id);
				
		  	     //ArtistButton a = new ArtistButton();
			     //a.setLabel(r.getString("artist_name"));
			     //a.artist = new Artist(r.getInt("artist_id"), "", r.getString("artist_name"), -1);
			     //GLOBAL.(a);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	closeConnToDB();
    }
    
    public static void Main(String[] args){
    	
    }

	@Override
	public void run() {
		//System.out.println("thread stated");

	}
}